#!/bin/bash

#输出路径
output_path=/db_backup/database_update
if [ ! -d $output_path ]
then
        mkdir -p $output_path
fi

if [ $# -ne 10 ]
then
	echo "usage: $0 <org_db_ip> <org_db_port> <org_db_username> <org_db_password> <org_db_database> <des_db_ip> <des_db_port> <des_db_username> <des_db_password> <des_db_database>"
	exit
fi

#源数据库配置信息
org_db_ip=$1			# 172.16.112.21
org_db_port=$2			# 3306
org_db_username=$3		# root
org_db_password=$4		# airport
org_db_database=$5		# db_oil_pvg

#目标数据库配置信息
des_db_ip=$6			# 172.16.112.21
des_db_port=$7			# 3306
des_db_username=$8		# root
des_db_password=$9		# airport
des_db_database=${10}		# db_oil_pvg_his

#配置通用命令
mysql_org="mysql -h$org_db_ip -P$org_db_port -u$org_db_username -p$org_db_password -D$org_db_database -N"
mysql_des="mysql -h$des_db_ip -P$des_db_port -u$des_db_username -p$des_db_password -D$des_db_database -N"
mysqldump_org="mysqldump -h$org_db_ip -P$org_db_port -u$org_db_username -p$org_db_password"
mysqldump_des="mysqldump -h$des_db_ip -P$des_db_port -u$des_db_username -p$des_db_password"

#1、将源数据库的所有表名称查找出来
sql_org="SELECT LOWER(TABLE_NAME) AS TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$org_db_database' AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME;"
tb_org=$($mysql_org -e"$sql_org")
#echo "$tb_org"

#2、将目标数据库的所有表名称查找出来
sql_des="SELECT LOWER(TABLE_NAME) AS TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$des_db_database' AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME;"
tb_des=$($mysql_des -e"$sql_des")
#echo "$tb_des"

#3、找出不存在的表名称
db_update_file="$output_path/$org_db_database-$(date '+%Y%m%d%H%M%S').sql"
#OLD_IFS="$IFS"
#IFS=$'\t'
echo "正在查找目标数据库中不查找的表名称...$(date '+%Y-%m-%d %H:%M:%S')"
table_new="" #不存在的表名称列表
table_old="" #已经存在的表名称列表
while read table_name_org
do
	flag=0
	#echo "org: $table_name_org"
	while read table_name_des
	do
		#echo "des: $table_name_des"
		if [ "$table_name_des" == "$table_name_org" ]
		then
			flag=1
			break
		fi
	done< <(echo "$tb_des")
	if [ "$flag" == "0" ]
	then
		table_new="$table_new,$table_name_org"
		echo "目标数据库中不存在表名称: $table_name_org"
		echo "正在导出[$table_name_org]的表结构...$(date '+%Y-%m-%d %H:%M:%S')"
		$mysqldump_org -d "$org_db_database" "$table_name_org">> $db_update_file
		if [ $? -ne 0 ]
		then
			echo "导出数据库[$org_db_database]表[$table_name_org]的表结构失败...$(date '+%Y-%m-%d %H:%M:%S')"
			exit
		fi
		echo "[$table_name_org]的表结构导出成功...$(date '+%Y-%m-%d %H:%M:%S')"
	else
		table_old="$table_old,$table_name_org"
	fi
done< <(echo "$tb_org")
#IFS="$OLD_IFS"
echo "表名称查找结束...$(date '+%Y-%m-%d %H:%M:%S')"

#echo -e "table_new:\n$table_new"
#echo -e "\ntable_old:\n$table_old"

#4、比对和同步表结构信息是否一致
if [ ${#table_old} -gt 0 ]
then
	table_old=${table_old:1}
	#echo $table_old
	echo "开始比对已经存在的表的结构信息是否一致...$(date '+%Y-%m-%d %H:%M:%S')"
	OLD_IFS="$IFS"
	IFS=","
	tb_arr=($table_old)
	IFS="$OLD_IFS"
	for tb in ${tb_arr[@]}
	do
		echo "正在比对数据库表[$tb]的所有字段是否一致...$(date '+%Y-%m-%d %H:%M:%S')"
		sql_tb_org="SELECT TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,(CASE WHEN IS_NULLABLE='YES' THEN 'NULL' ELSE 'NOT NULL' END) AS IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT,COLUMN_KEY,EXTRA 
        		FROM information_schema.\`COLUMNS\` 
        		WHERE TABLE_SCHEMA='$org_db_database' AND TABLE_NAME='$tb' 
        		ORDER BY COLUMN_NAME"
		sql_tb_des="SELECT TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,(CASE WHEN IS_NULLABLE='YES' THEN 'NULL' ELSE 'NOT NULL' END) AS IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT,COLUMN_KEY,EXTRA 
        		FROM information_schema.\`COLUMNS\` 
        		WHERE TABLE_SCHEMA='$des_db_database' AND TABLE_NAME='$tb' 
        		ORDER BY COLUMN_NAME"
		tb_info_org=$($mysql_org -e"$sql_tb_org")
		#echo "$tb_info_org"
		tb_info_des=$($mysql_des -e"$sql_tb_des")
		OLD_IFS="$IFS"
		IFS=$'\t'
		while read TABLE_NAME_ORG COLUMN_NAME_ORG COLUMN_TYPE_ORG IS_NULLABLE_ORG COLUMN_DEFAULT_ORG COLUMN_COMMENT_ORG COLUMN_KEY_ORG EXTRA_ORG
		do
        		flag=0
        		while read TABLE_NAME_DES COLUMN_NAME_DES COLUMN_TYPE_DES IS_NULLABLE_DES COLUMN_DEFAULT_DES COLUMN_COMMENT_DES COLUMN_KEY_DES EXTRA_DES
        		do
                		if [ "$COLUMN_NAME_ORG" == "$COLUMN_NAME_DES" ] # 先找到同一个字段名称
                		then
                        		flag=1
                        		if [[ "$COLUMN_TYPE_ORG" != "$COLUMN_TYPE_DES" || "$IS_NULLABLE_ORG" != "$IS_NULLABLE_DES" || "$COLUMN_DEFAULT_ORG" != "$COLUMN_DEFAULT_DES" 
						|| ( "$COLUMN_COMMENT_ORG" != "$COLUMN_COMMENT_DES" && "$COLUMN_COMMENT_ORG" != "" ) ]] # 发现字段属性不一致
                        		then
                                		tmp="ALTER TABLE $TABLE_NAME_ORG MODIFY COLUMN $COLUMN_NAME_ORG $COLUMN_TYPE_ORG $IS_NULLABLE_ORG"
                                		if [ "$COLUMN_DEFAULT_ORG" != "NULL" ]
                                		then
                                        		tmp=$tmp" DEFAULT $COLUMN_DEFAULT_ORG"
                                		else
                                        		if [ "$IS_NULLABLE_ORG" != "NOT NULL" ]
                                        		then
                                                		tmp=$tmp" DEFAULT $COLUMN_DEFAULT_ORG"
                                        		fi
                                		fi
                                		if [ "$COLUMN_COMMENT_ORG" != "NULL" ]
                                		then
                                        		tmp=$tmp" COMMENT '$COLUMN_COMMENT_ORG'"
                                		fi
                                		tmp=$tmp";"
                                		echo "$tmp" >> $db_update_file
                                		echo $tmp
                        		fi
                		fi
        		done< <(echo "$tb_info_des")
        		if [ $flag == 0 ]
        		then
                		echo "字段[$COLUMN_NAME_ORG}]在目标库[$des_db_database]的表[$tb]上不存在"
                		tmp="ALTER TABLE $TABLE_NAME_ORG ADD COLUMN $COLUMN_NAME_ORG $COLUMN_TYPE_ORG $IS_NULLABLE_ORG"
                		if [ "$COLUMN_DEFAULT_ORG" != "NULL" ]
                		then
                        		tmp=$tmp" DEFAULT $COLUMN_DEFAULT_ORG"
                		else
                        		if [ "$IS_NULLABLE_ORG" != "NOT NULL" ]
                        		then
                                		tmp=$tmp" DEFAULT $COLUMN_DEFAULT_ORG"
                        		fi
                		fi
                		if [ "$COLUMN_COMMENT_ORG" != "NULL" ]
                		then
                        		tmp=$tmp" COMMENT '$COLUMN_COMMENT_ORG'"
                		fi
                		tmp=$tmp";"
                		echo "$tmp" >> $db_update_file
                		echo "$tmp"
        		fi
		done< <(echo "$tb_info_org")
		IFS="$OLD_IFS"
	done
fi
echo "已经存在的表的结构信息比对结束...$(date '+%Y-%m-%d %H:%M:%S')"

#5、导入更新脚本
if [ -f $db_update_file ]
then
	echo "正在对目标数据库[$des_db_database]执行脚本升级操作...$(date '+%Y-%m-%d %H:%M:%S')"
	$mysql_des < $db_update_file
	if [ $? -ne 0 ]
	then
		echo "数据库表结构同步失败...$(date '+%Y-%m-%d %H:%M:%S')"
		exit
	fi
	echo "对目标数据库[$des_db_database]执行脚本升级操作已完成...$(date '+%Y-%m-%d %H:%M:%S')"
else
	echo "源数据库与目标数据库表结构已经一致，无需升级...$(date '+%Y-%m-%d %H:%M:%S')"
fi

echo "数据库表结构已经同步完成...$(date '+%Y-%m-%d %H:%M:%S')"
